package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import constants.Constants;
import domain.misc.Misc;

public class MiscDao {
	FileDao fileDao = new FileDao();

	String dbUrl = Constants.DB_URL;
	String dbUsername = Constants.DB_USERNAME;
	String dbPassword = Constants.DB_PASSWORD;
	
	public Misc getMiscDetails(int fileNo){
		Misc result = new Misc();
		String query = "select Unit, Analysis, Write_Pol, Write_Rpo, Start_Sea, Policy, Max_MIM, Cull_Milk, Cull_Margin, Interest, P_Loss, Inv_Cull_Pct, Funk, P_Calf, P_Fixed_Labor, P_Fixed_Other, P_Other_Dry, P_Other_Lact, P_Tmr_Dm_Dry, P_Tmr_Dm_Lac, Dmi_Dry, Pct_Fat, Pa_Cv, Milk_Repeat, P_Labor, Time_Fresh, Time_Lact, Time_Dry, Vet_Cost_1, Vet_Cost_2, Vet_Cost_3, Vet_Cost_4 from dbo.DVIPV_MISC where File_No = "+fileNo;
		
		Connection sqlConnection = null;
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e1) {
			e1.printStackTrace();
		}
		
		PreparedStatement pStmt = null;
		ResultSet  rs = null;
		try {
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			pStmt = sqlConnection.prepareStatement(query);
			//pStmt.setInt(1, fileNo);
			rs = pStmt.executeQuery();
			
			while(rs.next())
			{
				result.setUnit(rs.getInt("Unit"));
				result.setAnalysis(rs.getInt("Analysis"));
				result.setWritepol(rs.getInt("Write_Pol"));
				result.setWriterpo(rs.getInt("Write_Rpo"));
				result.setStart_sea(rs.getInt("Start_Sea"));
				result.setPolicy(rs.getInt("Policy"));
				result.setMaxmim(rs.getInt("Max_MIM"));
				result.setCullmilk(rs.getDouble("Cull_Milk"));
				result.setCullmargin(rs.getDouble("Cull_Margin"));
				result.setInterest(rs.getDouble("Interest"));
				result.setP_loss(rs.getDouble("P_Loss"));
				result.setInvcullpct(rs.getDouble("Inv_Cull_Pct"));
				result.setFunk(rs.getInt("Funk"));
				result.setP_calf(rs.getDouble("P_Calf"));
				result.setP_fixed_labor(rs.getDouble("P_Fixed_Labor"));
				result.setP_fixed_other(rs.getDouble("P_Fixed_Other"));
				result.setP_other_dry(rs.getDouble("P_Other_Dry"));
				result.setP_other_lact(rs.getDouble("P_Other_Lact"));
				result.setP_tmrdmdry(rs.getDouble("P_Tmr_Dm_Dry"));
				result.setP_tmrdmlac(rs.getDouble("P_Tmr_Dm_Lac"));
				result.setDmi_dry(rs.getDouble("Dmi_Dry"));
				result.setPctfat(rs.getDouble("Pct_Fat"));
				result.setPa_cv(rs.getDouble("Pa_Cv"));
				result.setMilkrepeat(rs.getDouble("Milk_Repeat"));
				result.setP_labor(rs.getDouble("P_Labor"));
				result.setTimefresh(rs.getDouble("Time_Fresh"));
				result.setTimelact(rs.getDouble("Time_Lact"));
				result.setTimedry(rs.getDouble("Time_Dry"));
				result.getVetcost()[1] = rs.getDouble("Vet_Cost_1");
				result.getVetcost()[2] = rs.getDouble("Vet_Cost_2");
				result.getVetcost()[3] = rs.getDouble("Vet_Cost_3");
				result.getVetcost()[4] = rs.getDouble("Vet_Cost_4");
			}
			rs.close();
			pStmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return result;
	}
	
	
	public boolean updateMiscDetails(int fileNo, Misc obj){
		boolean result = true;
		
		String query = "update DVIP_IN_MISC set Unit = ?, P_Calf = ?, P_Fixed_Other = ?, P_Other_Dry = ?, P_Other_Lact = ?, P_Tmr_Dm_Dry = ?, P_Tmr_Dm_Lac = ?, Dmi_Dry = ?, Vet_Cost_1 = ?, Vet_Cost_2 = ?, Vet_Cost_3 = ?, Vet_Cost_4 = ? where File_No = ?";
		
		Connection sqlConnection = null;
		PreparedStatement pStmt = null;
		
		int colIndex = 1;
		
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			sqlConnection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
			pStmt = sqlConnection.prepareStatement(query);
			
			pStmt.setDouble(colIndex++, obj.getUnit());
			pStmt.setDouble(colIndex++, obj.getP_calf());
			pStmt.setDouble(colIndex++, obj.getP_fixed_other());
			pStmt.setDouble(colIndex++, obj.getP_other_dry());
			pStmt.setDouble(colIndex++, obj.getP_other_lact());
			pStmt.setDouble(colIndex++, obj.getP_tmrdmdry());
			pStmt.setDouble(colIndex++, obj.getP_tmrdmlac());
			pStmt.setDouble(colIndex++, obj.getDmi_dry());
			pStmt.setDouble(colIndex++, obj.getVetcost()[1]);
			pStmt.setDouble(colIndex++, obj.getVetcost()[2]);
			pStmt.setDouble(colIndex++, obj.getVetcost()[3]);
			pStmt.setDouble(colIndex++, obj.getVetcost()[4]);
			pStmt.setInt(colIndex++, fileNo);
			
			pStmt.executeUpdate();
			
			pStmt.close();
			sqlConnection.close();
		} catch (SQLException e) {
			result = false;
			e.printStackTrace();
		} catch (ClassNotFoundException e1) {
			result = false;
			e1.printStackTrace();
		}
		
		return result;
	}
}
